﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ProdeManagmente.Libs;
using System.Data;
using ProdeManagmente.Model;
using System.Collections;

namespace ProdeManagmente.DAO
{
    public class TeamService
    {
        private static TeamService instance;
        private static String TBTeam = "Team";
        private static String TBTeamTournament = "TeamTournament";
        private TournamenteService tournamentService;

        private TeamService()
        {
            tournamentService = TournamenteService.getTournamenteServiceInstance();
        }

        public static TeamService getTeamServiceInstance()
        {
            if (instance == null)
            {
                instance = new TeamService();
            }
            return instance;
        }

        public Team getTeamById(long id)
        {
            Team team = null;
            Persistencia conn = Persistencia.getInstance();
            String SQL = "SELECT * FROM " + TBTeam + " " +
                            "WHERE ID = " + id;

            DataTable teamTable = conn.executeQuery(SQL);

            foreach (DataRow teamRow in teamTable.Rows)
            {
                team = getTeam(teamRow);
            }
            return team;
        }

        /**
         * Obtiene la lista de equipos,  filtro SIN la palabra clave WHERE
         */
        public List<Team> getTeamListFilter(String filter)
        {
            List<Team> teamList = new List<Team>();

            Persistencia conn = Persistencia.getInstance();
            String SQL = "SELECT * FROM " + TBTeam + " ";

            if (!filter.Trim().Equals(""))
            {
                SQL = SQL + "WHERE " + filter;
            }

            DataTable teamTable = conn.executeQuery(SQL);

            foreach (DataRow teamRow in teamTable.Rows)
            {
                Team team = getTeam(teamRow);
                teamList.Add(team);
            }
            return teamList;
        }

        public List<Tournament> getTournamentTeam(long idTeam)
        {
            List<Tournament> tournamentList = new List<Tournament>();
            

            Persistencia conn = Persistencia.getInstance();
            String SQL = "SELECT * FROM " + TBTeamTournament + " " +
                         "WHERE idTeam = " + idTeam + " ";

            DataTable tournamentTable = conn.executeQuery(SQL);

            foreach (DataRow tournamentRow in tournamentTable.Rows)
            {
                Tournament tournament = tournamentService.getTournamentById(Convert.ToInt64(tournamentRow["idTournament"].ToString()));
                tournamentList.Add(tournament);
            }
            return tournamentList;
        }

        public Team save(Team team)
        {
            Persistencia conn = Persistencia.getInstance();
            String SQL = "";
            if (team.getId() != 0L) //Si es diferente de null es por que ya existe entonces tiene que actualizarlo
            {
                SQL = "UPDATE  " + TBTeam + " SET " +
                      "name = '" + team.getName() + "', " +
                      "league = '" + team.getLeague() + "' " +
                      "WHERE id = " + team.getId() + " ";
            }
            else //Si es null es por que es un objeto nuevo
            {
                team.setId(getNewId());
                SQL = "INSERT INTO " + TBTeam + " " +
                       "(id, name, league) VALUES " +
                       "(" + team.getId() + ", '" + team.getName() + "', '" + team.getLeague() + "')";
            }

            int result = Convert.ToInt32(conn.ExecuteNonQuery(SQL));
            if (result <= 0)
            {
                throw new BusinessException("302", "No se pudo grabar el elemento");
            }

            return team;
        }

        public void delete(long id)
        {
            Persistencia conn = Persistencia.getInstance();
            String SQL = "";

            SQL = "DELETE  " + TBTeam + "  " +
                    "WHERE id = " + id + " ";

            int result = Convert.ToInt32(conn.ExecuteNonQuery(SQL));
        }

        public void deleteTournamentTeam(long idTeam)
        {
            try
            {
                Persistencia conn = Persistencia.getInstance();
                String SQL = "";

                SQL = "DELETE  " + TBTeamTournament + "  " +
                        "WHERE idTeam = " + idTeam + " ";

                int result = Convert.ToInt32(conn.ExecuteNonQuery(SQL));
            }
            catch (BusinessException e)
            {
                throw e;
            }
        }

        private Team getTeam(DataRow teamRow)
        {
            Team team = null;

            team = new Team(Convert.ToInt64(teamRow["id"].ToString()),
                            teamRow["name"].ToString(),
                            teamRow["league"].ToString(),
                            teamRow["pathFlag"].ToString());

            return team;
        }

        private long getNewId()
        {
            long id = 0L;
            String strId;
            Persistencia conn = Persistencia.getInstance();
            String SQL = "SELECT MAX(id) FROM " + TBTeam + " ";

            DataTable teamTable = conn.executeQuery(SQL);

            foreach (DataRow teamRow in teamTable.Rows)
            {
                strId = teamRow[0].ToString();
                if (strId.Equals(""))
                {
                    strId = "0";
                }
                id = Convert.ToInt64(strId) + 1;
            }
            return id;
        }
    }
}
